﻿package com.esm.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.esm.dao.UserDao;
import com.esm.model.User;
import com.esm.utils.JDBCUtil;
import net.sf.json.JSON;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;






/**
 * 
 * @author 王文成
 *
 */

public class UserDaoImpl implements UserDao{
	
	



	public User findUser(String uname, String upsw) 
	{
		//生成一个用户对象
		User user = new User();



		//数据库操作sql数据


		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_user");
		sql.append(" WHERE ems_user.uname=?");
		sql.append(" AND ems_user.upsw=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());



			//给SQL语句赋值


			pst.setString(1,uname);
			pst.setString(2,upsw);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				//初始化用户对象参数
				user.setUid(rs.getInt("uid"));
				user.setUname(rs.getString("uname"));
				user.setUpsw(rs.getString("upsw"));
				user.setRid(rs.getInt("rid"));
			}
		} 
		catch (SQLException e) 
		
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return user;
	}





	@Override
	public JSONObject searchUserInfo(int uid) {
		JSONObject user=new JSONObject();
		StringBuffer sql=new StringBuffer("SELECT * FROM ems_user ");
		sql.append(" WHERE uid=?");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setInt(1, uid);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				user.put("uname", rs.getString("uname"));
				user.put("usex",rs.getString("usex"));
				user.put("uage", rs.getString("uage"));
				user.put("uidcard", rs.getString("uidcard"));
				user.put("utelphone", rs.getString("utelphone"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user;
	}

	@Override
	public boolean updateSelfInfo(String uname, String usex, String uage, String utelphone, String uidcard, int uid) {
		boolean flag=false;
		StringBuffer sql=new StringBuffer("UPDATE ems_user ");
		sql.append(" SET uname=?,usex=?,uage=?,utelphone=?,uidcard=?");
		sql.append("WHERE uid=?");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setString(1, uname);
			ps.setString(2, usex);
			ps.setString(3, uage);
			ps.setString(4, utelphone);
			ps.setString(5, uidcard);
			ps.setInt(6, uid);
			int i=ps.executeUpdate();
			if(i!=0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public JSONArray getDefaultName(String rname) {
		JSONArray unames=new JSONArray();
		StringBuffer sql=new StringBuffer("SELECT ems_user.uname ,ems_user.uid");
		sql.append(" FROM ems_user");
		sql.append(" INNER JOIN ems_role");
		sql.append(" ON ems_user.rid=ems_role.rid");
		sql.append(" WHERE ems_role.rname=?");
		Connection conn=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=conn.prepareStatement(sql.toString());
			ps.setString(1, rname);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				JSONObject j=new JSONObject();
				j.put("uname", rs.getString("uname"));
				j.put("uid",rs.getInt("uid"));
				unames.add(j);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return unames;

	}

	public List<User> findAllEmployeesMessage(int rid,int start,int pageSize) 
	{
		List<User> emps = new ArrayList<User>();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_user");
		sql.append(" WHERE ems_user.rid=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			//SQL语句赋值
			pst.setInt(1, rid);
			pst.setInt(2, (start-1)*pageSize);
			pst.setInt(3, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				User user = new User();
				user.setUid(rs.getInt("uid"));
				user.setUname(rs.getString("uname"));
				user.setUpsw(rs.getString("upsw"));
				user.setUsex(rs.getString("usex"));
				user.setUage(rs.getString("uage"));
				user.setUidcard(rs.getString("uidcard"));
				user.setUtelphone(rs.getString("utelphone"));
				user.setDep_id(rs.getInt("dep_id"));
				user.setSal_id(rs.getInt("sal_id"));
				user.setRid(rs.getInt("rid"));
				emps.add(user);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return emps;
	}
	
	public JSONArray findAllEmployees(int rid, int start, int pageSize) 
	{
		JSONArray jao = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_user");
		sql.append(" WHERE ems_user.rid=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			//SQL语句赋值
			pst.setInt(1, rid);
			pst.setInt(2, (start-1)*pageSize);
			pst.setInt(3, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("uid", rs.getInt("uid"));
				jo.put("uname", rs.getString("uname"));
				jo.put("upsw", rs.getString("upsw"));
				jo.put("usex", rs.getString("usex"));
				jo.put("uage", rs.getString("uage"));
				jo.put("uidcard", rs.getString("uidcard"));
				jo.put("utelphone", rs.getString("utelphone"));
				jo.put("dep_id", rs.getInt("dep_id"));
				jo.put("sal_id", rs.getInt("sal_id"));
				jo.put("rid", rs.getInt("rid"));
				jao.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jao;
	}
	public List<User> findDeptEmployeesMessage(int rid,int dep_id,int start,int pageSize) 
	{
		List<User> empes = new ArrayList<User>();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_user");
		sql.append(" WHERE ems_user.rid=?");
		sql.append(" AND ems_user.dep_id=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			//SQL语句赋值
			pst.setInt(1, rid);
			pst.setInt(2, dep_id);
			pst.setInt(3, (start-1)*pageSize);
			pst.setInt(4, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				User user = new User();
				user.setUid(rs.getInt("uid"));
				user.setUname(rs.getString("uname"));
				user.setUpsw(rs.getString("upsw"));
				user.setUsex(rs.getString("usex"));
				user.setUage(rs.getString("uage"));
				user.setUidcard(rs.getString("uidcard"));
				user.setUtelphone(rs.getString("utelphone"));
				user.setDep_id(rs.getInt("dep_id"));
				user.setSal_id(rs.getInt("sal_id"));
				user.setRid(rs.getInt("rid"));
				empes.add(user);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return empes;
	}

	public int findEmpolyeesCount(int rid) 
	{
		int count = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(ems_user.uid) AS ucount");
		sql.append(" FROM ems_user");
		sql.append(" WHERE ems_user.rid=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			//SQL语句赋值
			pst.setInt(1, rid);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("ucount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}
	public int findDeptEmpolyeesCount(int rid, int dep_id) 
	{
		int count = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(ems_user.uid) AS ucount");
		sql.append(" FROM ems_user");
		sql.append(" WHERE ems_user.rid=?");
		sql.append(" AND ems_user.dep_id=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			//SQL语句赋值
			pst.setInt(1, rid);
			pst.setInt(2, dep_id);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("ucount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	public boolean insertEmployee(User addUser) 
	{
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ems_user (uname,upsw,usex,uage,uidcard,utelphone,dep_id,sal_id,rid)");
		sql.append(" VALUES (?,?,?,?,?,?,?,?,?)");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			//SQL语句赋值
			pst.setString(1, addUser.getUname());
			pst.setString(2, addUser.getUpsw());
			pst.setString(3, addUser.getUsex());
			pst.setString(4, addUser.getUage());
			pst.setString(5, addUser.getUidcard());
			pst.setString(6, addUser.getUtelphone());
			pst.setInt(7, addUser.getDep_id());
			pst.setInt(8, addUser.getSal_id());
			pst.setInt(9, addUser.getRid());
			//数据库执行查询语句
			int i = pst.executeUpdate();
			if(i > 0)
			{
				flag = true;
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return flag;
	}

	public boolean updateEmployee(int uid,User updateUser) 
	{
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ems_user SET");
		sql.append(" uname=?,upsw=?,usex=?,uage=?,uidcard=?,utelphone=?,dep_id=?,sal_id=?,rid=?");
		sql.append(" WHERE uid=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			//SQL语句赋值
			pst.setString(1, updateUser.getUname());
			pst.setString(2, updateUser.getUpsw());
			pst.setString(3, updateUser.getUsex());
			pst.setString(4, updateUser.getUage());
			pst.setString(5, updateUser.getUidcard());
			pst.setString(6, updateUser.getUtelphone());
			pst.setInt(7, updateUser.getDep_id());
			pst.setInt(8, updateUser.getSal_id());
			pst.setInt(9, updateUser.getRid());
			pst.setInt(10, uid);
			//数据库执行查询语句
			int i = pst.executeUpdate();
			if(i > 0)
			{
				flag = true;
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return flag;
	}

	public boolean deleteEmployee(int uid) 
	{
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM ems_user");
		sql.append(" WHERE uid=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			//SQL语句赋值
			pst.setInt(1, uid);
			//数据库执行查询语句
			int i = pst.executeUpdate();
			if(i > 0)
			{
				flag = true;
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return flag;
	}

	public int findDidByDname(String dname) 
	{
		int count = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ems_dept.dep_id");
		sql.append(" FROM ems_dept");
		sql.append(" where ems_dept.dep_name like ?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			//SQL语句赋值
			pst.setString(1, "%"+dname+"%");
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("dep_id");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	public JSONArray findDeptEmployees(int rid, int dep_id, int start, int pageSize) 
	{
		JSONArray jao = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_user");
		sql.append(" WHERE ems_user.rid=?");
		sql.append(" AND ems_user.dep_id=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, rid);
			pst.setInt(2, dep_id);
			pst.setInt(3, (start-1)*pageSize);
			pst.setInt(4, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("uid", rs.getInt("uid"));
				jo.put("uname", rs.getString("uname"));
				jo.put("upsw", rs.getString("upsw"));
				jo.put("usex", rs.getString("usex"));
				jo.put("uage", rs.getString("uage"));
				jo.put("uidcard", rs.getString("uidcard"));
				jo.put("utelphone", rs.getString("utelphone"));
				jo.put("dep_id", rs.getInt("dep_id"));
				jo.put("sal_id", rs.getInt("sal_id"));
				jo.put("rid", rs.getInt("rid"));
				jao.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jao;
	}

	public JSONArray findDempName(int rid, int dep_id) 
	{
		JSONArray jao = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ems_user.uid,ems_user.uname FROM ems_user");
		sql.append(" WHERE ems_user.rid=?");
		sql.append(" AND ems_user.dep_id=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, rid);
			pst.setInt(2, dep_id);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("uid", rs.getInt("uid"));
				jo.put("uname", rs.getString("uname"));
				jao.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jao;
	}

	public User findDempByUid(int uid) 
	{
		//生成一个用户对象
		User user = new User();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_user");
		sql.append(" WHERE ems_user.uid=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			//给SQL语句赋值
			pst.setInt(1,uid);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				//初始化用户对象参数
				user.setUid(rs.getInt("uid"));
				user.setUname(rs.getString("uname"));
				user.setUsex(rs.getString("usex"));
				user.setUage(rs.getString("uage"));
				user.setUidcard(rs.getString("uidcard"));
				user.setUtelphone(rs.getString("utelphone"));
				user.setDep_id(rs.getInt("dep_id"));
				user.setSal_id(rs.getInt("sal_id"));
			}
		} 
		catch (SQLException e) 
		
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return user;
	}

}
